/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package sqlgenerators.search;

import entities.Search;
import entities.SearchInterestAreaRequerimentAndOperator;

/**
 *
 * @author damfercer
 */
public class InterestAndResitrictionsSQLGenerator implements SearchSqlGeneratorInterface{
    private String res;
    @Override
    public String getSqlFromSearch(Search search) {
        res="";
        if(!search.getSearchInterestAreaRequerimentAndOperatorList().isEmpty()){
            res+=" AND (";
            int i=0;
            for(SearchInterestAreaRequerimentAndOperator restriction: search.getSearchInterestAreaRequerimentAndOperatorList()){
                if(i==0){
                    res+="TRUE";
                    res+=generateSearchInterestAreaRequerimentAndOperatorSQL(restriction);
                }
                else{
                    res+=generateSearchInterestAreaRequerimentAndOperatorSQL(restriction);
                }
                i++;
            }
            res+=")";
        }
        return res;
    }
    
    private String generateSearchInterestAreaRequerimentAndOperatorSQL(SearchInterestAreaRequerimentAndOperator restr){
        String result="";
        if(restr.getSector()!=null || restr.getJobPosition()!=null || restr.getMaximumDesiredSalary()!=null){
            result=" AND EXISTS(SELECT ID_CANDIDATE_INTEREST_AREA FROM candidate_interest_area WHERE candidate_interest_area.CANDIDATE=candidate.ID_CANDIDATE AND candidate_interest_area.DESIRED_SALARY=desired_salary.ID_DESIRED_SALARY";
            if(restr.getSector()!=null){
                result+=" AND candidate_interest_area.SECTOR="+restr.getSector().getIdSector();
            }
            if(restr.getJobPosition()!=null){
                result+=" AND candidate_interest_area.JOB_POSITION="+restr.getJobPosition().getIdJobPosition();            
            }
            if(restr.getMaximumDesiredSalary()!=null){
                result+=" AND desired_salary.VALUE<="+restr.getMaximumDesiredSalary().getValue();
            }
            result+=")";
        }
        return result;
    }
}
